const fs = require('fs');

// 生成随机数据函数
function getRandomInt(min, max) {
    return Math.floor(Math.random() * (max - min + 1)) + min;
}

function getRandomDecimal(min, max, decimals = 2) {
    return (Math.random() * (max - min) + min).toFixed(decimals);
}

function getRandomDate(start, end) {
    return new Date(start.getTime() + Math.random() * (end.getTime() - start.getTime()));
}

// 设备类型列表
const deviceTypes = ['基站', '路由器', '交换机', '中继器', '网关'];
// 位置列表
const locations = ['北京市朝阳区', '上海市浦东新区', '广州市天河区', '深圳市南山区', '杭州市西湖区', '成都市武侯区'];
// 任务类型列表
const taskTypes = ['频段优化', '功率调整', '信道分配', '天线调优', '参数优化'];

// 生成批量插入SQL
function generateInsertSQL() {
    let sqlContent = `-- 网络通信信号优化处理软件测试数据\n`;
    sqlContent += `-- 生成时间: ${new Date().toISOString()}\n\n`;

    // 1. 插入用户数据 (假设已有基础用户)
    sqlContent += `-- 用户数据 (假设已有基础数据)\n`;
    sqlContent += `-- INSERT INTO user (username, password) VALUES \n`;
    sqlContent += `-- ('admin', '加密密码'),\n`;
    sqlContent += `-- ('operator1', '加密密码'),\n`;
    sqlContent += `-- ('operator2', '加密密码');\n\n`;

    // 2. 插入设备数据
    sqlContent += `-- 设备数据\n`;
    sqlContent += `INSERT INTO device (device_name, device_type, location, status) VALUES\n`;
    const devices = [];
    for (let i = 1; i <= 10; i++) {
        const deviceType = deviceTypes[getRandomInt(0, deviceTypes.length - 1)];
        const location = locations[getRandomInt(0, locations.length - 1)];
        devices.push({
            id: i,
            name: `${deviceType}-${i}`,
            type: deviceType,
            location: location
        });
        sqlContent += `  ('${deviceType}-${i}', '${deviceType}', '${location}', ${getRandomInt(0, 1)})${i < 10 ? ',' : ';'}\n`;
    }
    sqlContent += `\n`;

    // 3. 插入信号采集数据
    sqlContent += `-- 信号采集数据\n`;
    sqlContent += `INSERT INTO signal_collection (device_id, signal_strength, snr_ratio, bandwidth, latency, packet_loss, collect_time) VALUES\n`;
    const totalSignalRecords = 50;
    for (let i = 1; i <= totalSignalRecords; i++) {
        const deviceId = getRandomInt(1, devices.length);
        const collectTime = getRandomDate(new Date(2024, 0, 1), new Date());
        
        sqlContent += `  (${deviceId}, ${getRandomDecimal(-120, -60)}, ${getRandomDecimal(10, 30)}, ${getRandomInt(20, 100)}, ${getRandomInt(10, 200)}, ${getRandomDecimal(0, 5, 2)}, '${collectTime.toISOString().slice(0, 19).replace('T', ' ')}')${i < totalSignalRecords ? ',' : ';'}\n`;
    }
    sqlContent += `\n`;

    // 4. 插入优化任务数据
    sqlContent += `-- 优化任务数据\n`;
    sqlContent += `INSERT INTO optimization_task (task_name, device_id, user_id, task_type, parameters, status, execute_time) VALUES\n`;
    const totalTasks = 15;
    const tasks = [];
    for (let i = 1; i <= totalTasks; i++) {
        const deviceId = getRandomInt(1, devices.length);
        const taskType = taskTypes[getRandomInt(0, taskTypes.length - 1)];
        const executeTime = getRandomDate(new Date(2024, 0, 1), new Date());
        const status = getRandomInt(0, 3);
        
        tasks.push({
            id: i,
            deviceId: deviceId,
            status: status
        });
        
        sqlContent += `  ('${taskType}任务-${i}', ${deviceId}, 1, '${taskType}', '{"frequency":"${getRandomInt(2400, 5800)}MHz","power":"${getRandomInt(10, 30)}dBm"}', ${status}, '${executeTime.toISOString().slice(0, 19).replace('T', ' ')}')${i < totalTasks ? ',' : ';'}\n`;
    }
    sqlContent += `\n`;

    // 5. 插入优化结果数据
    sqlContent += `-- 优化结果数据\n`;
    sqlContent += `INSERT INTO optimization_result (task_id, before_metrics, after_metrics, improvement_rate, optimization_details, result_status, execute_duration) VALUES\n`;
    const totalResults = 12;
    for (let i = 1; i <= totalResults; i++) {
        const taskId = getRandomInt(1, tasks.length);
        const resultStatus = getRandomInt(0, 1);
        const improvement = resultStatus ? getRandomDecimal(5, 50) : getRandomDecimal(-10, 0);
        
        sqlContent += `  (${taskId}, '{"signal_strength":${getRandomDecimal(-90, -70)},"latency":${getRandomInt(50, 150)}}', '{"signal_strength":${getRandomDecimal(-80, -60)},"latency":${getRandomInt(20, 100)}}', ${improvement}, '${taskTypes[getRandomInt(0, taskTypes.length - 1)]}优化完成', ${resultStatus}, ${getRandomInt(30, 300)})${i < totalResults ? ',' : ';'}\n`;
    }

    return sqlContent;
}

// 生成并保存SQL文件
const insertSQL = generateInsertSQL();
fs.writeFileSync('bulk_insert_data.sql', insertSQL, 'utf8');

console.log('批量插入SQL文件已生成: bulk_insert_data.sql');
console.log('包含数据:');
console.log('- 10个设备');
console.log('- 50条信号采集记录');
console.log('- 15个优化任务');
console.log('- 12个优化结果');
console.log('\n执行方法:');
console.log('1. 先执行 create_tables.sql 创建表结构');
console.log('2. 再执行 bulk_insert_data.sql 插入测试数据');